package com.team06.service.impl;

import com.team06.dao.DfdlDao;
import com.team06.dao.DfieDao;
import com.team06.dao.DfueDao;
import com.team06.pojo.FlightRouteHistory;
import com.team06.pojo.FlightRouteInfo;
import com.team06.pojo.PageResult;
import com.team06.pojo.TimeFormatUtils;
import com.team06.service.FlightSearchService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeParseException;
import java.util.*;
import java.util.stream.Collectors;

import static com.team06.pojo.TimeFormatUtils.UI_DATE_FORMATTER;

@Service
public class FlightSearchServiceImpl implements FlightSearchService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    //航班飞机全部查询
    //航班号，飞机编号，飞机机型，状态
    @Override
    public PageResult<Map<String, Object>> getFlightPlanes(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT f.FLNO, f.cfno, f.CFTP, f.STAT " +  // 这里添加了空格
                "FROM DFOE_DFDL_FLIGHT f LIMIT ? OFFSET ?";

        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, pageSize, offset);

        // 使用LinkedHashMap保持字段顺序
        List<Map<String, Object>> formattedData = data.stream()
                .map(row -> {
                    Map<String, Object> orderedMap = new LinkedHashMap<>();
                    orderedMap.put("FLNO", row.get("FLNO"));
                    orderedMap.put("cfno", row.get("cfno"));
                    orderedMap.put("CFTP", row.get("CFTP"));
                    orderedMap.put("STAT", row.get("STAT"));
                    return orderedMap;
                })
                .collect(Collectors.toList());

        return new PageResult<>(formattedData, page, pageSize, countFlightPlanes());
    }
    //航班飞机通过飞机编号查询
    @Override
    public PageResult<Map<String, Object>> getFlightsByAircraftId(String cfno, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT f.FLNO, f.cfno, f.CFTP, f.STAT FROM DFOE_DFDL_FLIGHT f WHERE f.cfno LIKE ? LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + cfno + "%", pageSize, offset);
        // 使用LinkedHashMap保持字段顺序
        List<Map<String, Object>> formattedData = data.stream()
                .map(row -> {
                    Map<String, Object> orderedMap = new LinkedHashMap<>();
                    orderedMap.put("FLNO", row.get("FLNO"));
                    orderedMap.put("cfno", row.get("cfno"));
                    orderedMap.put("CFTP", row.get("CFTP"));
                    orderedMap.put("STAT", row.get("STAT"));
                    return orderedMap;
                })
                .collect(Collectors.toList());
        long total = countFlightsByAircraftId(cfno);
        return PageResult.of(formattedData, page, pageSize, total);
    }
    //航班飞机通过飞机机型查询
    @Override
    public PageResult<Map<String, Object>> getFlightsByAircraftType(String cftp, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT f.FLNO, f.cfno, f.CFTP, f.STAT FROM DFOE_DFDL_FLIGHT f WHERE f.CFTP LIKE ? LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + cftp + "%", pageSize, offset);
        // 使用LinkedHashMap保持字段顺序
        List<Map<String, Object>> formattedData = data.stream()
                .map(row -> {
                    Map<String, Object> orderedMap = new LinkedHashMap<>();
                    orderedMap.put("FLNO", row.get("FLNO"));
                    orderedMap.put("cfno", row.get("cfno"));
                    orderedMap.put("CFTP", row.get("CFTP"));
                    orderedMap.put("STAT", row.get("STAT"));
                    return orderedMap;
                })
                .collect(Collectors.toList());
        long total = countFlightsByAircraftType(cftp);
        return PageResult.of(formattedData, page, pageSize, total);
    }

    // 查询所有航班时间信息的方法
    @Override
    public PageResult<Map<String, Object>> queryFlightTimeInfo(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT f.FLNO, f.FIDE, a.FPTT, a.FPLT, f.STAT " +
                "FROM DFOE_DFDL_FLIGHT f JOIN DFOE_DFDL_AIRPORT a ON f.ID = a.FLIGHT_ID LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, pageSize, offset);
        long total = countFlightTimeInfo();
        return PageResult.of(data, page, pageSize, total);
    }

    // 通过航班号查询航班时间信息的方法
    public PageResult<Map<String, Object>> queryFlightTimeInfoByFlightNumber(String flightNumber, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT f.FLNO, f.FIDE, a.FPTT, a.FPLT, f.STAT " +
                "FROM DFOE_DFDL_FLIGHT f JOIN DFOE_DFDL_AIRPORT a ON f.ID = a.FLIGHT_ID " +
                "WHERE f.FLNO LIKE ? LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + flightNumber + "%", pageSize, offset);
        long total = countFlightTimeInfoByFlightNumber(flightNumber);
        return PageResult.of(data, page, pageSize, total);
    }
    //航班机场信息查询
    // 查询所有机场信息，三字代码，机场属性，中文描述，英文描述，机场是否开启，航站简称
    @Override
    public PageResult<Map<String, Object>> getAllAirports(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT APOT_CODE, APOT_APAT, APOT_CNNM, APOT_ENNM, APOT_AISO, APOT_APSN " +
                "FROM MSG_BASE_APUE LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, pageSize, offset);
        long total = countAllAirports();
        return PageResult.of(data, page, pageSize, total);
    }

    // 通过机场中文名查询机场信息
    @Override
    public PageResult<Map<String, Object>> getAirportsByCnName(String cnnm, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT APOT_CODE, APOT_APAT, APOT_CNNM, APOT_ENNM, APOT_AISO, APOT_APSN " +
                "FROM MSG_BASE_APUE WHERE APOT_CNNM LIKE ? LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + cnnm + "%", pageSize, offset);
        long total = countAirportsByCnName(cnnm);
        return PageResult.of(data, page, pageSize, total);
    }

    // 通过机场英文名查询机场信息
    @Override
    public PageResult<Map<String, Object>> getAirportsByEnName(String ennm, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT APOT_CODE, APOT_APAT, APOT_CNNM, APOT_ENNM, APOT_AISO, APOT_APSN " +
                "FROM MSG_BASE_APUE WHERE APOT_ENNM LIKE ? LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + ennm + "%", pageSize, offset);
        long total = countAirportsByEnName(ennm);
        return PageResult.of(data, page, pageSize, total);
    }
    //航班航线查询 全查
    @Override
    public PageResult<FlightRouteInfo> getFlightRoutes(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT al.DFLT_FLID AS FLIGHT_NUMBER, al.AIRL_ARPT_ID AS ROUTE_INFO " +
                "FROM MSG_DFME_AIRL al LIMIT ? OFFSET ?";

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, pageSize, offset);
        List<FlightRouteInfo> flightRoutes = new ArrayList<>();

        for (Map<String, Object> row : rows) {
            Integer flightNumberInt = (Integer) row.get("FLIGHT_NUMBER");
            String flightNumber = flightNumberInt != null ? flightNumberInt.toString() : "";
            String routeInfo = (String) row.get("ROUTE_INFO");

            if (routeInfo == null || routeInfo.isEmpty()) {
                continue;
            }

            String[] stations = routeInfo.split(",");
            if (stations.length < 2) {
                continue;
            }

            String[] departureParts = stations[0].split(":");
            String departureStation = departureParts.length > 0 ? departureParts[0] : "";
            String flightAttribute = departureParts.length > 1 ? departureParts[1] : "";

            String[] destinationParts = stations[stations.length - 1].split(":");
            String destinationStation = destinationParts.length > 0 ? destinationParts[0] : "";

            List<String> stopoverStations = new ArrayList<>();
            for (int i = 1; i < stations.length - 1; i++) {
                String[] stopParts = stations[i].split(":");
                if (stopParts.length > 0) {
                    stopoverStations.add(stopParts[0]);
                }
            }

            flightRoutes.add(new FlightRouteInfo(
                    flightNumber,
                    flightAttribute,
                    departureStation,
                    destinationStation,
                    stopoverStations
            ));
        }

        long total = countFlightRoutes();
        return PageResult.of(flightRoutes, page, pageSize, total);
    }
//航班航线查询，通过航班号
    @Override
    public List<FlightRouteInfo> getFlightRouteByFlightNumber(String flightNumber) {
        String sql = "SELECT al.DFLT_FLID AS FLIGHT_NUMBER, al.AIRL_ARPT_ID AS ROUTE_INFO " +
                "FROM MSG_DFME_AIRL al " +
                "WHERE TO_CHAR(al.DFLT_FLID) LIKE ?";

        try {
            List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, "%" + flightNumber + "%");
            List<FlightRouteInfo> routes = new ArrayList<>();

            for (Map<String, Object> row : rows) {
                Integer flightNumberInt = (Integer) row.get("FLIGHT_NUMBER");
                String currentFlightNumber = flightNumberInt != null ? flightNumberInt.toString() : flightNumber;
                String routeInfo = (String) row.get("ROUTE_INFO");

                if (routeInfo == null || routeInfo.isEmpty()) {
                    continue;
                }

                String[] stations = routeInfo.split(",");
                if (stations.length < 2) {
                    continue;
                }

                String[] departureParts = stations[0].split(":");
                String departureStation = departureParts.length > 0 ? departureParts[0] : "";
                String flightAttribute = departureParts.length > 1 ? departureParts[1] : "";

                String[] destinationParts = stations[stations.length - 1].split(":");
                String destinationStation = destinationParts.length > 0 ? destinationParts[0] : "";

                List<String> stopoverStations = new ArrayList<>();
                for (int i = 1; i < stations.length - 1; i++) {
                    String[] stopParts = stations[i].split(":");
                    if (stopParts.length > 0) {
                        stopoverStations.add(stopParts[0]);
                    }
                }

                routes.add(new FlightRouteInfo(
                        currentFlightNumber,
                        flightAttribute,
                        departureStation,
                        destinationStation,
                        stopoverStations
                ));
            }

            return routes;
        } catch (NumberFormatException e) {
            throw new IllegalArgumentException("航班号必须是数字");
        }
    }

    //航班航线变更历史查询

    // 解析航线信息的方法
    private FlightRouteHistory parseRouteHistory(Map<String, Object> row) {
        FlightRouteHistory history = new FlightRouteHistory();

        history.setHistoryId((Integer) row.get("HISTORY_ID"));
        history.setAirlId((Integer) row.get("AIRL_ID"));
        history.setFlightNumber(row.get("FLIGHT_NUMBER") != null ? row.get("FLIGHT_NUMBER").toString() : "");
        history.setOldRouteInfo((String) row.get("OLD_AIRL_ARPT_ID"));
        history.setNewRouteInfo((String) row.get("NEW_AIRL_ARPT_ID"));
        history.setUpdateTime((Date) row.get("UPDATE_TIME"));
        history.setUpdatedBy((String) row.get("UPDATED_BY"));
        history.setApprovalStatus((String) row.get("APPROVAL_STATUS"));

        // 解析旧航线信息
        if (history.getOldRouteInfo() != null) {
            String[] oldStations = history.getOldRouteInfo().split(",");
            if (oldStations.length > 0) {
                String[] departureParts = oldStations[0].split(":");
                history.setOldDeparture(departureParts.length > 0 ? departureParts[0] : "");

                if (oldStations.length > 1) {
                    String[] destinationParts = oldStations[oldStations.length - 1].split(":");
                    history.setOldDestination(destinationParts.length > 0 ? destinationParts[0] : "");

                    List<String> oldStopovers = new ArrayList<>();
                    for (int i = 1; i < oldStations.length - 1; i++) {
                        String[] stopParts = oldStations[i].split(":");
                        if (stopParts.length > 0) {
                            oldStopovers.add(stopParts[0]);
                        }
                    }
                    history.setOldStopovers(oldStopovers);
                }
            }
        }

        // 解析新航线信息
        if (history.getNewRouteInfo() != null) {
            String[] newStations = history.getNewRouteInfo().split(",");
            if (newStations.length > 0) {
                String[] departureParts = newStations[0].split(":");
                history.setNewDeparture(departureParts.length > 0 ? departureParts[0] : "");

                if (newStations.length > 1) {
                    String[] destinationParts = newStations[newStations.length - 1].split(":");
                    history.setNewDestination(destinationParts.length > 0 ? destinationParts[0] : "");

                    List<String> newStopovers = new ArrayList<>();
                    for (int i = 1; i < newStations.length - 1; i++) {
                        String[] stopParts = newStations[i].split(":");
                        if (stopParts.length > 0) {
                            newStopovers.add(stopParts[0]);
                        }
                    }
                    history.setNewStopovers(newStopovers);
                }
            }
        }

        return history;
    }

    @Override
    public PageResult<FlightRouteHistory> getAllRouteHistories(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT h.HISTORY_ID, h.AIRL_ID, a.DFLT_FLID AS FLIGHT_NUMBER, " +
                "h.OLD_AIRL_ARPT_ID, h.NEW_AIRL_ARPT_ID, h.UPDATE_TIME, h.UPDATED_BY, a.APPROVAL_STATUS " +
                "FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "ORDER BY h.UPDATE_TIME DESC LIMIT ? OFFSET ?";

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, pageSize, offset);
        List<FlightRouteHistory> histories = new ArrayList<>();

        for (Map<String, Object> row : rows) {
            histories.add(parseRouteHistory(row));
        }

        long total = countAllRouteHistories();
        return PageResult.of(histories, page, pageSize, total);
    }

    @Override
    public PageResult<FlightRouteHistory> getRouteHistoriesByFlightNumber(String flightNumber, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT h.HISTORY_ID, h.AIRL_ID, a.DFLT_FLID AS FLIGHT_NUMBER, " +
                "h.OLD_AIRL_ARPT_ID, h.NEW_AIRL_ARPT_ID, h.UPDATE_TIME, h.UPDATED_BY, a.APPROVAL_STATUS " +
                "FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "WHERE TO_CHAR(a.DFLT_FLID) LIKE ? " +
                "ORDER BY h.UPDATE_TIME DESC LIMIT ? OFFSET ?";

        // 添加通配符进行模糊查询
        String searchPattern = "%" + flightNumber + "%";

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, searchPattern, pageSize, offset);
        List<FlightRouteHistory> histories = new ArrayList<>();

        for (Map<String, Object> row : rows) {
            histories.add(parseRouteHistory(row));
        }

        long total = countRouteHistoriesByFlightNumber(flightNumber);
        return PageResult.of(histories, page, pageSize, total);
    }

    @Override
    public PageResult<FlightRouteHistory> getRouteHistoriesByNewDeparture(String departure, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT h.HISTORY_ID, h.AIRL_ID, a.DFLT_FLID AS FLIGHT_NUMBER, " +
                "h.OLD_AIRL_ARPT_ID, h.NEW_AIRL_ARPT_ID, h.UPDATE_TIME, h.UPDATED_BY, a.APPROVAL_STATUS " +
                "FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "WHERE h.NEW_AIRL_ARPT_ID LIKE ? " +
                "ORDER BY h.UPDATE_TIME DESC LIMIT ? OFFSET ?";

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, departure + ":%", pageSize, offset);
        List<FlightRouteHistory> histories = new ArrayList<>();

        for (Map<String, Object> row : rows) {
            histories.add(parseRouteHistory(row));
        }

        long total = countRouteHistoriesByNewDeparture(departure);
        return PageResult.of(histories, page, pageSize, total);
    }

//    @Override
//    public PageResult<FlightRouteHistory> getRouteHistoriesByNewStopover(String destination, int page, int pageSize) {
//        int offset = (page - 1) * pageSize;
//        String sql = "SELECT h.HISTORY_ID, h.AIRL_ID, a.DFLT_FLID AS FLIGHT_NUMBER, " +
//                "h.OLD_AIRL_ARPT_ID, h.NEW_AIRL_ARPT_ID, h.UPDATE_TIME, h.UPDATED_BY, a.APPROVAL_STATUS " +
//                "FROM MSG_DFME_AIRL_HISTORY h " +
//                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
//                "WHERE h.NEW_AIRL_ARPT_ID LIKE ? " +
//                "ORDER BY h.UPDATE_TIME DESC LIMIT ? OFFSET ?";
//
//        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, "%:" + destination, pageSize, offset);
//        List<FlightRouteHistory> histories = new ArrayList<>();
//
//        for (Map<String, Object> row : rows) {
//            histories.add(parseRouteHistory(row));
//        }
//
//        long total = countRouteHistoriesByNewDestination(destination);
//        return PageResult.of(histories, page, pageSize, total);
//    }
@Override
public PageResult<FlightRouteHistory> getRouteHistoriesByNewStopover(String stopover, int page, int pageSize) {
    int offset = (page - 1) * pageSize;
    String sql = "SELECT h.HISTORY_ID, h.AIRL_ID, a.DFLT_FLID AS FLIGHT_NUMBER, " +
            "h.OLD_AIRL_ARPT_ID, h.NEW_AIRL_ARPT_ID, h.UPDATE_TIME, h.UPDATED_BY, a.APPROVAL_STATUS " +
            "FROM MSG_DFME_AIRL_HISTORY h " +
            "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
            "WHERE h.NEW_AIRL_ARPT_ID LIKE ? OR h.NEW_AIRL_ARPT_ID LIKE ? OR h.NEW_AIRL_ARPT_ID LIKE ? " +
            "ORDER BY h.UPDATE_TIME DESC LIMIT ? OFFSET ?";

    // Search for stopover in different positions in the route string
    List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql,
            "%:" + stopover + ",%",    // middle of route (e.g., :SHA,CSX)
            "%:" + stopover + "%",     // end of route (e.g., :SHA)
            "%," + stopover + ":%",    // alternative format if exists
            pageSize, offset);

    List<FlightRouteHistory> histories = new ArrayList<>();
    for (Map<String, Object> row : rows) {
        histories.add(parseRouteHistory(row));
    }

    long total = countRouteHistoriesByNewStopover(stopover);
    return PageResult.of(histories, page, pageSize, total);
}



    @Override
    public PageResult<FlightRouteHistory> getRouteHistoriesByNewDestination(String stopover, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT h.HISTORY_ID, h.AIRL_ID, a.DFLT_FLID AS FLIGHT_NUMBER, " +
                "h.OLD_AIRL_ARPT_ID, h.NEW_AIRL_ARPT_ID, h.UPDATE_TIME, h.UPDATED_BY, a.APPROVAL_STATUS " +
                "FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "WHERE h.NEW_AIRL_ARPT_ID LIKE ? OR h.NEW_AIRL_ARPT_ID LIKE ? " +
                "ORDER BY h.UPDATE_TIME DESC LIMIT ? OFFSET ?";

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql,
                "%," + stopover + ":%", "%:" + stopover + ",%", pageSize, offset);
        List<FlightRouteHistory> histories = new ArrayList<>();

        for (Map<String, Object> row : rows) {
            histories.add(parseRouteHistory(row));
        }

        long total = countRouteHistoriesByNewStopover(stopover);
        return PageResult.of(histories, page, pageSize, total);
    }

    // 计数方法实现
    @Override
    public long countAllRouteHistories() {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    @Override
    public long countRouteHistoriesByFlightNumber(String flightNumber) {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "WHERE TO_CHAR(a.DFLT_FLID) LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + flightNumber + "%");
    }

    @Override
    public long countRouteHistoriesByNewDeparture(String departure) {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "WHERE h.NEW_AIRL_ARPT_ID LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, departure + ":%");
    }

    @Override
    public long countRouteHistoriesByNewDestination(String destination) {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "WHERE h.NEW_AIRL_ARPT_ID LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%:" + destination);
    }
    @Override
    public long countRouteHistoriesByNewStopover(String stopover) {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_AIRL_HISTORY h " +
                "JOIN MSG_DFME_AIRL a ON h.AIRL_ID = a.DFLT_FLID " +
                "WHERE h.NEW_AIRL_ARPT_ID LIKE ? OR h.NEW_AIRL_ARPT_ID LIKE ? OR h.NEW_AIRL_ARPT_ID LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class,
                "%:" + stopover + ",%",
                "%:" + stopover + "%",
                "%," + stopover + ":%");
    }


    //行李转盘查询

    @Override
    public PageResult<Map<String, Object>> getAllBllsBelts(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT m.DFLT_FLID, b.BELT_ID, b.BTNO, b.CODE, b.ESTR, b.EEND, b.RSTR, b.REND " +
                "FROM DFME_BLLS_BELT b " +
                "JOIN MSG_DFME_BLLS m ON b.BELT_ID = m.BLLS_BELT_ID " +
                "LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, pageSize, offset);
        long total = countAllBllsBelts();
        return PageResult.of(data, page, pageSize, total);
    }

    @Override
    public PageResult<Map<String, Object>> getBllsBeltsByFlightId(String flid, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT m.DFLT_FLID, b.BELT_ID, b.BTNO, b.CODE, b.ESTR, b.EEND, b.RSTR, b.REND " +
                "FROM DFME_BLLS_BELT b " +
                "JOIN MSG_DFME_BLLS m ON b.BELT_ID = m.BLLS_BELT_ID " +
                "WHERE TO_CHAR(m.DFLT_FLID) LIKE ? " +
                "LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + flid + "%", pageSize, offset);
        long total = countBllsBeltsByFlightId(flid);
        return PageResult.of(data, page, pageSize, total);
    }

    @Override
    public long countAllBllsBelts() {
        String sql = "SELECT COUNT(*) FROM DFME_BLLS_BELT";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    @Override
    public long countBllsBeltsByFlightId(String flid) {
        String sql = "SELECT COUNT(DISTINCT b.BELT_ID) FROM DFME_BLLS_BELT b " +
                "JOIN MSG_DFME_BLLS m ON b.BELT_ID = m.BLLS_BELT_ID " +
                "WHERE TO_CHAR(m.DFLT_FLID) LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + flid + "%");
    }
    //登机口查询
    //查询航班号FLID，登机口序号GTNO,登机口编号CODE,登机口所属楼号BTSC，登机口开始时间RSTR，登机口结束时间REND

    @Override
    public PageResult<Map<String, Object>> getAllBoardingGates(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT DFLT_FLID AS FLID, GTNO, CODE, BTSC, RSTR, REND " +
                "FROM MSG_DFME_GTLS " +
                "ORDER BY META_DDTM ASC LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, pageSize, offset);
        long total = countAllBoardingGates();
        return PageResult.of(data, page, pageSize, total);
    }

    @Override
    public PageResult<Map<String, Object>> getBoardingGatesByFlightId(String flid, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT DFLT_FLID AS FLID, GTNO, CODE, BTSC, RSTR, REND " +
                "FROM MSG_DFME_GTLS " +
                "WHERE TO_CHAR(DFLT_FLID) LIKE ? " +
                "ORDER BY META_DDTM ASC LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + flid + "%", pageSize, offset);
        long total = countBoardingGatesByFlightId(flid);
        return PageResult.of(data, page, pageSize, total);
    }

    @Override
    public PageResult<Map<String, Object>> getBoardingGatesByCode(String code, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT DFLT_FLID AS FLID, GTNO, CODE, BTSC, RSTR, REND " +
                "FROM MSG_DFME_GTLS " +
                "WHERE CODE LIKE ? " +
                "ORDER BY META_DDTM ASC LIMIT ? OFFSET ?";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, "%" + code + "%", pageSize, offset);
        long total = countBoardingGatesByCode(code);
        return PageResult.of(data, page, pageSize, total);
    }

    @Override
    public long countAllBoardingGates() {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_GTLS";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    @Override
    public long countBoardingGatesByFlightId(String flid) {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_GTLS WHERE TO_CHAR(DFLT_FLID) LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + flid + "%");
    }

    @Override
    public long countBoardingGatesByCode(String code) {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_GTLS WHERE CODE LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + code + "%");
    }


    // 计数方法实现
    @Override
    public long countFlightPlanes() {
        String sql = "SELECT COUNT(*) FROM DFOE_DFDL_FLIGHT";
        Long count = jdbcTemplate.queryForObject(sql, Long.class);
        System.out.println("总记录数: " + count); // 添加调试日志
        return count;
    }

    @Override
    public long countFlightsByAircraftId(String cfno) {
        String sql = "SELECT COUNT(*) FROM DFOE_DFDL_FLIGHT WHERE cfno LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + cfno + "%");
    }

    @Override
    public long countFlightsByAircraftType(String cftp) {
        String sql = "SELECT COUNT(*) FROM DFOE_DFDL_FLIGHT WHERE CFTP LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + cftp + "%");
    }

    @Override
    public long countFlightTimeInfo() {
        String sql = "SELECT COUNT(*) FROM DFOE_DFDL_FLIGHT f JOIN DFOE_DFDL_AIRPORT a ON f.ID = a.FLIGHT_ID";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    @Override
    public long countFlightTimeInfoByFlightNumber(String flightNumber) {
        String sql = "SELECT COUNT(*) FROM DFOE_DFDL_FLIGHT f " +
                "JOIN DFOE_DFDL_AIRPORT a ON f.ID = a.FLIGHT_ID WHERE f.FLNO LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + flightNumber + "%");
    }

    @Override
    public long countAllAirports() {
        String sql = "SELECT COUNT(*) FROM MSG_BASE_APUE";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    @Override
    public long countAirportsByCnName(String cnnm) {
        String sql = "SELECT COUNT(*) FROM MSG_BASE_APUE WHERE APOT_CNNM LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + cnnm + "%");
    }

    @Override
    public long countAirportsByEnName(String ennm) {
        String sql = "SELECT COUNT(*) FROM MSG_BASE_APUE WHERE APOT_ENNM LIKE ?";
        return jdbcTemplate.queryForObject(sql, Long.class, "%" + ennm + "%");
    }

    @Override
    public long countFlightRoutes() {
        String sql = "SELECT COUNT(*) FROM MSG_DFME_AIRL al";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }
    //起降架次统计
    @Override
    public List<Map<String, Object>> getDailyFlightStatistics(String startDate, String endDate) {
        try {
            // 转换日期格式
            String dbStartTime = TimeFormatUtils.convertToDatabaseStartTime(startDate);
            String dbEndTime = TimeFormatUtils.convertToDatabaseEndTime(endDate);

            // 查询到达航班
            String arrivalSql = "SELECT DFLT_FRLT FROM MSG_DFME_ARRE " +
                    "WHERE DFLT_FRLT IS NOT NULL AND LENGTH(DFLT_FRLT) = 14 " +
                    "AND DFLT_FRLT BETWEEN ? AND ?";

            // 查询起飞航班
            String departureSql = "SELECT DFLT_FRTT FROM MSG_DFME_DEPE " +
                    "WHERE DFLT_FRTT IS NOT NULL AND LENGTH(DFLT_FRTT) = 14 " +
                    "AND DFLT_FRTT BETWEEN ? AND ?";

            // 查询数据
            List<String> arrivalTimes = jdbcTemplate.queryForList(arrivalSql, String.class,
                    dbStartTime, dbEndTime);
            List<String> departureTimes = jdbcTemplate.queryForList(departureSql, String.class,
                    dbStartTime, dbEndTime);

            // 处理结果
            Map<String, Map<String, Integer>> resultMap = new TreeMap<>();

            // 处理到达航班
            processFlightTimes(arrivalTimes, "arrivals", resultMap);

            // 处理起飞航班
            processFlightTimes(departureTimes, "departures", resultMap);

            // 转换为前端需要的格式
            return convertResultToResponseFormat(resultMap, startDate, endDate);

        } catch (DateTimeParseException e) {
            throw new IllegalArgumentException("日期格式不正确，请使用yyyy-MM-dd格式");
        }
    }

    // 处理航班时间数据
    private void processFlightTimes(List<String> timeStrs, String type,
                                    Map<String, Map<String, Integer>> resultMap) {
        for (String timeStr : timeStrs) {
            LocalDateTime dateTime = TimeFormatUtils.parse14DigitTime(timeStr);
            if (dateTime == null) continue;

            String dateKey = dateTime.format(UI_DATE_FORMATTER);

            resultMap.computeIfAbsent(dateKey, k -> {
                Map<String, Integer> map = new HashMap<>();
                map.put("arrivals", 0);
                map.put("departures", 0);
                return map;
            }).merge(type, 1, Integer::sum);
        }
    }

    // 转换结果为响应格式
    private List<Map<String, Object>> convertResultToResponseFormat(
            Map<String, Map<String, Integer>> resultMap,
            String startDate, String endDate) {

        List<Map<String, Object>> result = new ArrayList<>();
        LocalDate start = LocalDate.parse(startDate);
        LocalDate end = LocalDate.parse(endDate);

        // 确保包含日期范围内的所有日期
        for (LocalDate date = start; !date.isAfter(end); date = date.plusDays(1)) {
            String dateStr = date.format(UI_DATE_FORMATTER);
            Map<String, Integer> counts = resultMap.getOrDefault(dateStr,
                    Map.of("arrivals", 0, "departures", 0));

            result.add(Map.of(
                    "flight_date", dateStr,
                    "arrivals", counts.get("arrivals"),
                    "departures", counts.get("departures")
            ));
        }

        return result;
    }
}



